using System;
using System.Data;
using System.Collections.Generic;
namespace eTMS.DAO
{
    /********************************BEGIN DiemDanhSinhVienDAO****************************************/
    ///<summary>
    /// <para>Author: Auto generated class by CSharp Code Generator v2.0</para>
    /// <para>Date:13/10/2011 41:04:34</para>
    /// <para>Class Name: DiemDanhSinhVienDAO</para>
    /// <para>Description: Class access to table DiemDanhSinhVien in database. Class for coder code here</para>
    ///</summary>


    public partial class DiemDanhSinhVienDAO
    {
        public DataSet SelectAllDSWithLopMonHoc(int HocKy_ID, int NamHoc_ID, DateTime? TuNgay, DateTime? DenNgay)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        DiemDanhSinhVien.*, Lop.TenLop, MonHoc.TenMonHoc
                                FROM            DiemDanhSinhVien INNER JOIN
                                                         Lop ON DiemDanhSinhVien.Lop_ID = Lop.Lop_ID INNER JOIN
                                                         MonHoc ON DiemDanhSinhVien.MonHoc_ID = MonHoc.MonHoc_ID";
            cmd.CommandText += " WHERE HocKy_ID=" + HocKy_ID.ToString() + " AND NamHoc_ID=" + NamHoc_ID.ToString();
            if (TuNgay.HasValue)
            {
                cmd.CommandText += " AND DiemDanhSinhVien.NgayDiemDanh>=@TuNgay";
                this.CreateCommandParameter(cmd, "@TuNgay", BeginDay(TuNgay.Value));
            }
            if (DenNgay.HasValue)
            {
                cmd.CommandText += " AND DiemDanhSinhVien.NgayDiemDanh<=@DenNgay";
                this.CreateCommandParameter(cmd, "@DenNgay", EndDay(DenNgay.Value));
            }
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSSinhVienSoLanNghiHoc(int HocKy_ID, int NamHoc_ID, int MonHoc_ID, string Filter)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        SinhVienNghiHoc.SinhVien_ID, COUNT(SinhVienNghiHoc.SinhVienNghiHoc_ID) AS SoLanNghi
                                FROM            SinhVienNghiHoc INNER JOIN
                                                         DiemDanhSinhVien ON SinhVienNghiHoc.DiemDanhSinhVien_ID = DiemDanhSinhVien.DiemDanhSinhVien_ID
                                WHERE        (DiemDanhSinhVien.HocKy_ID = @HocKy_ID) AND (DiemDanhSinhVien.NamHoc_ID = @NamHoc_ID) AND (DiemDanhSinhVien.MonHoc_ID = @MonHoc_ID)
                                " + (Filter.Length > 0 ? "AND " + Filter : "") + @"
                                GROUP BY SinhVienNghiHoc.SinhVien_ID";
            this.CreateCommandParameter(cmd, "@HocKy_ID", HocKy_ID);
            this.CreateCommandParameter(cmd, "@NamHoc_ID", NamHoc_ID);
            this.CreateCommandParameter(cmd, "@MonHoc_ID", MonHoc_ID);
            return this.SelectDS(cmd);
        }

    }
    /*****************************************************************************************************/
    /**********************************END DiemDanhSinhVienDAO*****************************************/
    /*****************************************************************************************************/
}

